from spider.data_source.jsl.jsl_api import get_bond_detail_history
import pandas as pd
import time
from utils.utils import get_pre
import os
import random
import datetime
from tqdm import tqdm
import qstock as qs
from utils.pd_show_utils import df_table

def get_bond_base_one_(pre_bond_id):
    """
    使用qstock数据源获取可转债基本历史数据(开、收、高、低)
    :param db:
    :param pre_bond_id:
    :return:
    """
    bond_history_pd = qs.get_data(pre_bond_id)
    if len(bond_history_pd) == 0:
        print(pre_bond_id + ' 完全获取失败')
        return pre_bond_id

    if bond_history_pd.isnull().any().any():
        print("存在缺失值 需要单独处理")

    del bond_history_pd['volume']
    del bond_history_pd['name']
    del bond_history_pd['code']
    del bond_history_pd['turnover']
    del bond_history_pd['turnover_rate']
    bond_history_pd = bond_history_pd.reset_index()
    return bond_history_pd




def get_bond_base_one(db, pre_bond_id):
    """
    使用qstock数据源获取可转债基本历史数据(开、收、高、低)
    :param db:
    :param pre_bond_id:
    :return:
    """
    bond_history_pd = qs.get_data(pre_bond_id[2:])
    if len(bond_history_pd) == 0:
        print(pre_bond_id + ' 完全获取失败')
        return pre_bond_id

    if bond_history_pd.isnull().any().any():
        print("存在缺失值 需要单独处理")

    del bond_history_pd['volume']
    del bond_history_pd['name']
    del bond_history_pd['code']
    del bond_history_pd['turnover']
    del bond_history_pd['turnover_rate']
    bond_history_pd = bond_history_pd.reset_index()
    try:
        # old_bond_history_pd = pd.read_sql_table(pre_bond_id, con=db.engine)
        # 获得旧数据的最晚日期
        # old_data_last_day = old_bond_history_pd.iloc[-1].date
        # 计算增强数据
        # df_update = bond_history_pd.loc[bond_history_pd['date'] > old_data_last_day]
        # 增量数据写入数据库
        bond_history_pd.to_sql(pre_bond_id,
                               con=db.engine,
                               if_exists='replace',
                               index=False)
    except ValueError as e:
        # 表格为空 直接创建
        bond_history_pd.to_sql(pre_bond_id,
                               con=db.engine,
                               if_exists='replace',
                               index=False)

    except IndexError as e:
        # print(pre_bond_id)
        # print(e)
        # 表格为空 直接创建
        bond_history_pd.to_sql(pre_bond_id,
                               con=db.engine,
                               if_exists='replace',
                               index=False)


def get_bond_base_all(db):
    """
    获取所有可转债(alive & dead)的历史数据(开、收、高、低)
    :param db:
    :return:
    """

    error_list = []

    # 查询所有alive
    pd_alive_bonds = pd.read_sql_table("jsl_bonds_alive", con=db.engine)
    # 迭代获取
    print("获取现存可转债基础历史数据")
    for index in range(0, len(pd_alive_bonds)):
        # 取出bond_id
        bond_id = pd_alive_bonds.bond_id[index]
        # 排除掉待上市可转债
        if pd_alive_bonds.price_tips[index] == "待上市":
            continue
        # 取出前缀 sh sz
        pre = pd_alive_bonds.market_cd[index][0:2]
        # 拼接成带前缀的bond_id
        pre_bond_id = pre + bond_id
        # 获取可转债历史数据
        error_code = get_bond_base_one(db, pre_bond_id)
        if error_code is not None:
            error_list.append(error_code)
        # 随机休息 避免被封
        time.sleep(random.random())
    return error_list

    # 查询所有dead
    print("获取已退市可转债基础历史数据")
    pd_dead_bonds = pd.read_sql_table("jsl_bonds_dead", con=db.engine)
    # for index in tqdm(range(0, len(pd_dead_bonds))):
    for index in range(0, len(pd_dead_bonds)):
        pre_bond_id = get_pre(pd_dead_bonds.stock_id[index]) + pd_dead_bonds.bond_id[index]
        error_code = get_bond_base_one(db, pre_bond_id)
        if error_code is not None:
            if pd_dead_bonds.delist_notes[index] != "撤销发行":
                error_list.append(error_code)
        time.sleep(random.random())

    return error_list

# 获得可转债详细历史数据-—>SQL
def _get_bond_pro_all(db,pd_bonds):
    """
    从集思录获取可转债高级详细历史数据
    :param db:
    :param pd_bonds:
    :return:
    """
    error_list = []
    for index in range(0, len(pd_bonds)):

        bond_id = pd_bonds.bond_id[index]
        # 拼接得到带前缀的可转债代码
        if 'market_cd' in pd_bonds.keys():
            pre_bond_id = pd_bonds.market_cd[index][0:2] + bond_id
        else:
            pre_bond_id = get_pre(pd_bonds.stock_id[index]) + bond_id
        # 调用接口 获取急集思录上详细数据
        premium_rate_list = get_bond_detail_history(db,bond_id)
        if premium_rate_list is None:
            print("可转债溢价率等详细数据获取失败-->", pre_bond_id)
            if 'price_tips' in list(pd_bonds.columns):
                if pd_bonds.price_tips[index] != "待上市":
                    error_list.append(pre_bond_id)
            if 'delist_notes' in list(pd_bonds.columns):
                if pd_bonds.delist_notes[index] != "撤销发行":
                    if pre_bond_id not in ['sh110009']:
                        error_list.append(pre_bond_id)
            continue
        try:
            if len(premium_rate_list) == 0:
                print(pre_bond_id + ' 完全获取失败')
                return pre_bond_id

            bond_history_data = pd.read_sql_table(pre_bond_id, con=db.engine)
            # 接口返回数据日期数据类型转换
            premium_rate_list['date'] = pd.to_datetime(premium_rate_list['date'])
            # 取出历史数据
            part1 = pd.DataFrame(bond_history_data, columns=['date', 'open', 'high', 'low', 'close'])
            # 直接将历史详细数据与历史数据合并
            result = pd.merge(part1, premium_rate_list, how='left', on='date')

            result['premium_rate'] = result['premium_rate'].replace('-', '0%')
            result['ytm_rt'] = result['ytm_rt'].replace('-', '0%')
            result['premium_rate'] = result['premium_rate'].str[:-1]
            result['ytm_rt'] = result['ytm_rt'].str[:-1]
            result = result.replace('-', 0)
            result = result.fillna(-1000)
            result = result.astype({'volume':'float64',
                                    'convert_value':'float64',
                                    'ytm_rt':'float64',
                                    'premium_rate':'float64',
                                    'curr_iss_amt':'float64',
                                    'turnover_rt':'float64'
                           })
            # 存入数据库
            result.to_sql(pre_bond_id,
                          con=db.engine,
                          if_exists='replace',
                          index=False)
            time.sleep(random.random())
        except ValueError as error:
            error_list.append(pre_bond_id)

    return error_list

def get_bond_pro_all(db):
    """
    获取可转债(alive & dead)的高级数据
    :param db:
    :return:
    """
    error_list = []
    print("获取现存可转债详细历史数据")
    pd_alive_bonds = pd.read_sql_table("jsl_bonds_alive", con=db.engine)
    error_list_part1 = _get_bond_pro_all(db, pd_alive_bonds)
    return error_list_part1

    print("获取以退市可转债详细历史数据")
    pd_dead_bonds = pd.read_sql_table("jsl_bonds_dead", con=db.engine)
    error_list_part2 = _get_bond_pro_all(db, pd_dead_bonds)
    error_list.extend(error_list_part1)
    error_list.extend(error_list_part2)
    return error_list


def get_table_zcfz():
    """
    获得资产负债表 并进行相应格式化或处理
    :return:
    """
    df = qs.financial_statement('资产负债表')
    df['货币资金'] = round(df['货币资金']/100000000, 3)
    df['应收账款'] = round(df['应收账款']/100000000, 3)
    df['存货'] = round(df['存货']/100000000, 3)
    df['总资产'] = round(df['总资产']/100000000, 3)
    df['应付账款'] = round(df['应付账款']/100000000, 3)
    df['预收账款'] = round(df['预收账款']/100000000, 3)
    df['总负债'] = round(df['总负债']/100000000, 3)
    df['股东权益'] = round(df['股东权益']/100000000, 3)
    df.rename(columns={'货币资金': '货币资金(亿)', '总资产': '总资产(亿)',
                       '应收账款': '应收账款(亿)', '存货': '存货(亿)',
                       '应付账款': '应付账款(亿)', '预收账款': '预收账款(亿)',
                       '总负债': '总负债(亿)', '股东权益': '股东权益(亿)',
                       }, inplace=True)
    return df
    df_table(df)


def get_table_lr():
    """
    获得利润表 并进行相应格式化或处理
    :return:
    """
    df = qs.financial_statement('利润表')
    df['净利润'] = round(df['净利润']/100000000, 4)
    df['营业总收入'] = round(df['营业总收入']/100000000, 4)
    df['营业支出'] = round(df['营业支出']/100000000, 4)
    df['销售费用'] = round(df['销售费用']/100000000, 4)
    df['管理费用'] = round(df['管理费用']/100000000, 4)
    df['财务费用'] = round(df['财务费用']/100000000, 4)
    df['营业利润'] = round(df['营业利润']/100000000, 4)
    df['营业总支出'] = round(df['营业总支出']/100000000, 4)
    df['利润总额'] = round(df['利润总额']/100000000, 4)

    df.rename(columns={'净利润': '净利润(亿)', '营业总收入': '营业总收入(亿)',
                       '营业支出': '营业支出(亿)', '销售费用': '销售费用(亿)',
                       '管理费用': '管理费用(亿)', '财务费用': '财务费用(亿)',
                       '营业总支出': '营业总支出(亿)', '利润总额': '利润总额(亿)', '营业利润': '营业利润(亿)',
                       }, inplace=True)
    return df

    df_table(df)


def get_table_xjll():
    """
    获得现金流量 并进行相应格式化或处理
    :return:
    """
    # TODO 暂时看不懂呢。。
    df = qs.financial_statement('现金流量表')
    # df['净利润'] = round(df['净利润']/100000000, 4)
    # df['营业总收入'] = round(df['营业总收入']/100000000, 4)
    # df['营业支出'] = round(df['营业支出']/100000000, 4)
    # df['销售费用'] = round(df['销售费用']/100000000, 4)
    # df['管理费用'] = round(df['管理费用']/100000000, 4)
    # df['财务费用'] = round(df['财务费用']/100000000, 4)
    # df['营业利润'] = round(df['营业利润']/100000000, 4)
    # df['营业总支出'] = round(df['营业总支出']/100000000, 4)
    # df['利润总额'] = round(df['利润总额']/100000000, 4)
    #
    # df.rename(columns={'净利润': '净利润(亿)', '营业总收入': '营业总收入(亿)',
    #                    '营业支出': '营业支出(亿)', '销售费用': '销售费用(亿)',
    #                    '管理费用': '管理费用(亿)', '财务费用': '财务费用(亿)',
    #                    '营业总支出': '营业总支出(亿)', '利润总额': '利润总额(亿)', '营业利润': '营业利润(亿)',
    #                    }, inplace=True)
    df_table(df)
if __name__ == '__main__':
    # df = qs.financial_statement('现金流量表')

    get_table_xjll()

    # df.head()


    # from api.model import DbHandler, r_db
    # db = DbHandler()
    # db.create_session()
    # _get_one_bond(db,'sh113597')
    # _get_one_bond_pro(db,'sz128074')
    # check_data(db)
